from flask import jsonify,request,Blueprint,make_response
from flask import current_app as app
from db import db1

from utlis.comm import get_data
from utlis.myfiles import myf
from utlis.myes import Myes

from xlrd import open_workbook
from io import BytesIO
from urllib.parse import quote

import datetime,json
import xlsxwriter
import mimetypes
import os

#蓝图
bp_company = Blueprint('company',__name__)

#写入日志测试(错误日志)
@bp_company.route('/test')
async def test():
    str="请求ip:"+request.remote_addr +',请求的url'+request.url+'错误信息'+'\n'
    app.logger.error(str)
    return "ok"

#############公司开始

#上传公司名字
@bp_company.route('/addcomany',methods = ['POST','GET'])
def addcomany():
    if request.method == 'POST':
        data = get_data()
        sql1 = """select * from comany where name = '%s'"""%(data['name'])
        res = db1.find_one(sql1)
        if res:
            return jsonify({'code': 10010,"mes":'已经有该公司了'})
        else:
            #时间格式化
            nowtime = datetime.datetime.strftime(datetime.datetime.now(),"%Y-%m-%d %H:%M:%S")
            #添加数据库
            sql = """insert into comany(name,pid,ztype,organcode,createpeople,createtime) values('%s',0,%d,'%s','曹宝福','%s')"""%(data['name'],int(data['ztype']),data['organcode'],nowtime)
            db1.insert(sql)
            return jsonify({'code':200})
    else:
        sql = """select * from comany"""
        res = db1.find_all(sql)
        return jsonify({'code':200,"list":res})

#文件上传
@bp_company.route('/fileM',methods = ['POST','GET'])
def fileM():
    if request.method == 'POST':
        #获取文件
        file = request.files['file']
        #重命名
        newname = myf.rename(file.filename)
        #找到文件夹下的文件
        file.save(os.path.join('./static/upload/'+ newname))
        #打开文件读取
        name = './static/upload/' + newname
        bk = open_workbook(name, encoding_override='utf-8')
        #读取行与列
        sh = bk.sheets()[0]
        nrows = sh.nrows
        ncols = sh.ncols
        #循环行（2代表从第二行开始是数据）
        for i in range(2, nrows):
            row_date = sh.row_values(i)
            #循环列
            for j in range(0,ncols):
                print(row_date[j])
        return 'ok'


#文件（excil）导出
@bp_company.route('/download', methods=["GET", "POST"])
def download():
    # 实例化二进制数据
    out = BytesIO()
    # 创建一个Excel实例
    workbook = xlsxwriter.Workbook(out)
    # 添加一个sheet，add_worksheet()，默认"Sheet1"，可自定义命名，add_worksheet("资产列表")
    table = workbook.add_worksheet()
    # table.write(行, 列, 字段名)
    # 行、列从 0 开始计数
    # 字段命名根据每个人使用情况而定，插入数据部分也是
    table.write(0, 0, "资产编号")
    table.write(0, 1, "资产名称")
    workbook.close()
    filename = quote("资产列表_{}.xlsx".format(datetime.datetime.now().strftime("%Y%m%d%H%M%S")))
    file = make_response(out.getvalue())
    out.close()
    mime_type = mimetypes.guess_type('%s.xlsx' % filename)[0]
    file.headers['Content-Type'] = mime_type
    file.headers["Cache-Control"] = "no-cache"
    file.headers['Content-Disposition'] = "attachment; filename*=utf-8''{}".format(filename)
    return file


#树状结构
def xtree(data):
    list = []
    dict = {}
    if data:
        for i in data:
            #重命明把name构造成label
            i['label'] = i['name']
            #以id为键构造数据
            dict[i['id']] = i
        #循环data看他是顶级父类
        for j in data:
            if j['pid'] == 0:
                j['label']=j['label']
                list.append(j)
            else:
                #如果他下面没有子类给他下面加一个空列表
                if 'children' not in dict[j['pid']]:
                    dict[j['pid']]['children'] = []
                #有的话直接加数据
                dict[j['pid']]['children'].append(j)
        return list


#无限极分类
@bp_company.route('/comantytree',methods=['POST','GET'])
def comantytree():
    if request.method == 'GET':
        list = []
        sql = """select * from comany"""
        res = db1.find_all(sql)
        list1 = xtree(res)
        return jsonify({'code': 200, "list": list1})

##es使用
# 创建索引(也就是插入数据)
@bp_company.route('/estestinsert')
def estest():
    es = Myes('testtabel','test-type')
    try:
        sql = "select * from comany limit 0,5"
        data = db1.find_all(sql)
        j=-1
        for i in data:
            j+=1
            es.insert(i)
        return 'true'
    except:
        print('error')
        return 'false'

# 查询数据根据字
@bp_company.route('/esget')
def esget():
    es = Myes('testtabel','test-type')
    body = {
        #查询一个字的全部数据
           "match":{
            "name":"财务"
        }
        # "multi_match":{
        #     "query":"abc",
        #     "fields":["name", "content"]
        # }
    }
    data = es.search(body)
    address_data = data['hits']['hits']
    print(address_data)
    return 'ok'

#获取角色
@bp_company.route('/position')
def position():
    sql = """select * from position"""
    res = db1.find_all(sql)
    return jsonify({'code':200,"list":res})

#获取岗位
@bp_company.route('/jobsM')
def jobsM():
    sql = """select * from jobs"""
    res = db1.find_all(sql)
    return jsonify({'code':200,"list":res})






















